package com.ldy.job.utils;

import cn.hutool.core.io.FileUtil;
import com.alibaba.excel.EasyExcel;
import com.ldy.job.ReportUtilsApplication;
import com.ldy.job.listener.NoModel2DataListener;
import com.ldy.job.listener.NoModel3DataListener;
import com.ldy.job.listener.NoModelDataListener;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

import static com.ldy.job.listener.NoModelDataListener.*;

@Slf4j
public class ExcelUtils {
    public static String RESSTR_YEWUYUNXINGFENXI = "彬雨：<br>" + "&nbsp;&nbsp;&nbsp;&nbsp;“单一窗口”部分内容请参考如下信息：<br><br>" +
            "&nbsp;&nbsp;&nbsp;&nbsp;" + getLastYear() + "年" + getLastMonth() + "月，“单一窗口”线上签单#N#张；" + getLastYear() + "年1-" + getLastMonth() +
            "月，“单一窗口”累计线上签单#N#张，同比增长#N#，#N#家机构100%通过“单一窗口”签单。<br>" + "&nbsp;&nbsp;&nbsp;&nbsp;截止" + getLastYear() +
            "年" + getLastMonth() + "月底，公司通过“单一窗口”数据授权的累计客户数达到#X#家，其中有效授权客户达到#X#家。小微信保易有效授权客户达到#X#家，授权比例已达#X#；" +
            "中小企业有效授权客户#X#家，授权比例为#X#；综合险有效授权客户#X#家，授权比例为#X#。<br><br>" +
//            "&nbsp;&nbsp;&nbsp;&nbsp;营业机构中，小微信保易有效客户授权比例超过信保易整体授权比例的有${xby_count}家：${xby_context}；<br>" +
//            "&nbsp;&nbsp;&nbsp;&nbsp;中小企业有效客户授权比例超过中小企业整体授权比例的有${zxq_count}家：${zxq_context}；<br>" +
//            "&nbsp;&nbsp;&nbsp;&nbsp;综合险有效客户授权比例超过综合险整体授权比例的有${zhx_count}家：${zhx_context}。<br><br>" +
            "<strong>&nbsp;&nbsp;&nbsp;&nbsp;数金资讯表格已经更新至附件，请查收。</strong>";

    public static String RESSTR_TUIGUANGYUEBAO = "&nbsp;&nbsp;&nbsp;&nbsp;截止" + getLastYear() + "年" + getLastMonth() +
            "月底，公司通过“单一窗口”数据授权的累计客户数达到#X#家，有效授权客户比例达到#X#。" +
            "其中小微信保易有效授权客户达到#X#家，授权比例已达#X#；中小企业有效授权客户#X#家，授权比例为#X#；综合险有效授权客户#X#家，授权比例#X#。具体授权情况详见附件。<br><br>"+
            "&nbsp;&nbsp;&nbsp;&nbsp;"+getLastYear()+"年1-"+getLastMonth()+"月，#X##X#家营业机构积极应用“单一窗口”数据对接小微操作平台模式，" +
            "推动#X#家企业使用“单一窗口”数据厘定保额。<br><br>" +
            "&nbsp;&nbsp;&nbsp;&nbsp;为积极落实中央巡视关于保额整改的相关工作要求，公司中小企业、信保易产品“承保金额”统计口径即将于2025年正式调整，“单一窗口”数据作为“承保金额”统计的重要来源，对于业务的平稳过渡和开展至关重要。各营业机构务必要紧抓变革“窗口期”，紧盯“单一窗口”数据授权进展，按照“应授权尽授权、应对接尽对接”的工作目标，加快“存量未授权”客户的实时排査和引导，持续提升客户授权比例，助力“承保金额”口径调整等相关工作落地实施。";

    public static String RESSTR_XIANSHANGHUALV = "各位领导同事， <br>" + "请阅" + getLastYear() + "年" + getLastMonth() +
            "月“单一窗口”及信保易线上签单情况月报。重点关注如下：<br>" + "&nbsp;&nbsp;&nbsp;&nbsp;√" + getLastYear() + "年" + getLastMonth() +
            "月，小微信保易线上签单#X#张（其中“单一窗口”签单#X#张），全渠道累计签单#X#张，信保易线上业务占比达到#X#。" + getLastYear() + "年1-" + getLastMonth() +
            "月，小微信保易线上签单#X#张（其中“单一窗口”签单#X#张），全渠道累计签单#X#张，信保易线上业务占比达到#X#。<br>" + "&nbsp;&nbsp;&nbsp;&nbsp;√截止" + getLastYear() +
            "年" + getLastMonth() + "月底，公司通过“单一窗口”数据授权的贸易险客户数达到#X#家，其中有效客户数达到#X#家。小微信保易产品授权客户数达#X#家，授权比例已达#X#；中小企业产品授权客户#X#家，" +
            "授权比例为#X#；综合险产品授权客户#X#家，授权比例#X#。<br>" + "&nbsp;&nbsp;&nbsp;&nbsp;√#X##X#家营业机构积极应用“单一窗口”数据对接小微操作平台模式，推动#X#家企业使用“单一窗口”数据厘定保额。<br>" +
            "具体月报详见附件，请大家及时参阅。感谢工作支持！";

    /**
     * 最简单的读
     * 1、替换日期
     * 2、检查是否超过100%
     * 3、检查附件月份
     * 4、检查标记颜色的数据条件是否出错
     */
    public static void simpleRead() {
        String fileName1 = ReportUtilsApplication.DOC_PATH + "\\替换\\小微信保易投保情况分析月报.xlsx";
        String fileName2 = ReportUtilsApplication.DOC_PATH + "\\替换\\授权情况分析结果表.xlsx";
        // 这里默认读取第一个sheet
        EasyExcel.read(fileName1, new NoModelDataListener()).sheet().doRead();
        EasyExcel.read(fileName2, new NoModel2DataListener()).sheet().doRead();
        EasyExcel.read(fileName2, new NoModel3DataListener()).sheet().doRead();
        Map<String, String> doSum = doSum();
        RESSTR_XIANSHANGHUALV = RESSTR_XIANSHANGHUALV.replaceFirst("#X#", doSum.get("lj_sn")).replaceFirst("#X#", doSum.get("lj_c")).replaceFirst("#X#", doSum.get("lj_st"));
        RESSTR_TUIGUANGYUEBAO = RESSTR_TUIGUANGYUEBAO.replaceFirst("#X#", doSum.get("lj_sn")).replaceFirst("#X#", doSum.get("lj_c")).replaceFirst("#X#", doSum.get("lj_st"));
        RESSTR_YEWUYUNXINGFENXI = RESSTR_YEWUYUNXINGFENXI.replaceFirst("#N#", doCompare(23, 55) + "");
        FileUtil.writeUtf8String(RESSTR_XIANSHANGHUALV + "<br><br>" + RESSTR_TUIGUANGYUEBAO + "<br><br>" + RESSTR_YEWUYUNXINGFENXI, ReportUtilsApplication.DOC_PATH + "\\生成\\email.html");
        ExcelToImg.doJob();
        ReplacePicture.doJob();
        //doSendEmail();
    }

    private static void doSendEmail() {
        String sender = "liudy_sj@sinosure.cn";
        //数金资讯
        SendEmailWithAttachment.doSend(new String[]{ReportUtilsApplication.DOC_PATH + "\\生成\\“单一窗口”推广应用月报(" + getLastMonth() + "月).doc",
                        ReportUtilsApplication.DOC_PATH + "\\替换\\单一窗口客户数据授权情况.xlsx"}, "《数金资讯》" + getLastYear() + "年第" + getMonth() + "期-应用推广通报栏目 数据需求",
                RESSTR_YEWUYUNXINGFENXI, new String[]{sender});
        try {
            Thread.sleep(5000);
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        }
        //线上签单
        SendEmailWithAttachment.doSend(new String[]{ReportUtilsApplication.DOC_PATH + "\\生成\\“单一窗口”及信保易线上签单情况月报(" + getLastMonth() + "月).pptx",
                        ReportUtilsApplication.DOC_PATH + "\\替换\\单一窗口客户数据授权情况.xlsx"}, "\"单一窗口\"及信保易线上签单情况月报（" + getLastYear() + "年" + getLastMonth() + "月）",
                RESSTR_XIANSHANGHUALV, new String[]{sender});
        try {
            Thread.sleep(5000);
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        }
        //分析素材
        SendEmailWithAttachment.doSend(new String[]{ReportUtilsApplication.DOC_PATH + "\\替换\\单一窗口客户数据授权情况.xlsx",
                ReportUtilsApplication.DOC_PATH + "\\替换\\小微信保易保单全渠道生效明细表（非提前审批）.xlsx"}, "关于协助提供" + getMonth() + "月业务运行分析报告相关素材的通知",
                RESSTR_TUIGUANGYUEBAO, new String[]{sender});
    }

    public static Map<String, String> doCount() {
        String excelFilePath = ReportUtilsApplication.DOC_PATH + "\\替换\\授权情况分析结果表.xlsx";
        HashMap<String, String> map = new HashMap<>();
        try (FileInputStream fis = new FileInputStream(excelFilePath); Workbook workbook = WorkbookFactory.create(fis)) {
            Sheet sheet = workbook.getSheetAt(0); // 获取第一个Sheet
            map.put("zhx_count", getCellCountDetail(sheet, 6) + "");
            map.put("zhx_context", getCellContextDetail(sheet, 6));
            map.put("zxq_count", getCellCountDetail(sheet, 9) + "");
            map.put("zxq_context", getCellContextDetail(sheet, 9));
            map.put("xby_count", getCellCountDetail(sheet, 12) + "");
            map.put("xby_context", getCellContextDetail(sheet, 12));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        System.out.println(map);
        return map;
    }

    private static int getCellCountDetail(Sheet sheet, int columnToCheck) {
        int cellCount = 0;
        int lastRowNum = sheet.getLastRowNum();
        Row lastRow = sheet.getRow(lastRowNum);
        Cell cellLast = lastRow.getCell(columnToCheck);
        double numericCellValue = cellLast.getNumericCellValue();
        for (int rowNum = 3; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                Cell cell = row.getCell(columnToCheck);
                if (cell != null) {
                    double stringCellValue = cell.getNumericCellValue();
                    if (stringCellValue > numericCellValue) {
                        cellCount++;
                    }
                }
            }
        }
        return cellCount;
    }

    private static String getCellContextDetail(Sheet sheet, int columnToCheck) {
        StringBuffer cellContext = new StringBuffer();
        int lastRowNum = sheet.getLastRowNum();
        Row lastRow = sheet.getRow(lastRowNum);
        Cell cellLast = lastRow.getCell(columnToCheck);
        double numericCellValue = cellLast.getNumericCellValue();
        for (int rowNum = 3; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                Cell cell = row.getCell(columnToCheck);
                if (cell != null) {
                    // 检查单元格是否有背景颜色
                    double stringCellValue = cell.getNumericCellValue();
                    if (stringCellValue > numericCellValue) {
                        cellContext = cellContext.append("、" + row.getCell(0).toString().replace("分公司", ""));
                    }
                }
            }
        }
        return cellContext.substring(1).toString();
    }

    public static Map<String, String> doSum() {
        String excelFilePath = ReportUtilsApplication.DOC_PATH + "\\替换\\小微信保易保单全渠道生效明细表（非提前审批）.xlsx";
        HashMap<String, String> map = new HashMap<>();
        try (FileInputStream fis = new FileInputStream(excelFilePath); Workbook workbook = WorkbookFactory.create(fis)) {
            Sheet sheet = workbook.getSheetAt(1); // 获取第一个Sheet
            map.put("lj_c", sheet.getLastRowNum() + "");
            map.put("lj_s", getCellSumHigh(sheet, 1) + "");
            map.put("lj_sn", getCellSum(sheet, 1)[1]);
            map.put("lj_st", amountTool(getCellSum(sheet, 1)[0]));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        System.out.println(map);
        return map;
    }

    private static int getCellSumHigh(Sheet sheet, int columnToCheck) {
        return keepTwoHighestDigits(Integer.parseInt(getCellSum(sheet, columnToCheck)[0]));
    }

    private static String[] getCellSum(Sheet sheet, int columnToCheck) {
        int cellCount = 0;
        String[] res = new String[2];
        StringBuilder sb = new StringBuilder();
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                Cell cell = row.getCell(columnToCheck);
                Cell cell_name = row.getCell(0);
                if(rowNum<=3){
                    sb.append(cell_name.getStringCellValue()).append("、");
                }
                if (cell != null) {
                    cellCount += cell.getNumericCellValue();
                }
            }
        }
        res[0] = cellCount+"";
        if(sheet.getLastRowNum()>3){
            res[1] = sb.substring(0,sb.length()-1).replace("分公司","")+"等";
        }else {
            res[1] = sb.substring(0,sb.length()-1).replace("分公司","");
        }
        return res;
    }

    public static int keepTwoHighestDigits(int number) {
        // 将数字转换成字符串
        String numberStr = String.valueOf(number);
        // 如果字符串长度小于或等于2，则直接返回原数字
        if (numberStr.length() <= 2) {
            return number;
        }
        StringBuilder strEnd = new StringBuilder();
        for (int i = 0; i < numberStr.length() - 2; i++) {
            strEnd.append("0");
        }
        // 截取字符串的前两个字符，并转换回整数
        return Integer.parseInt(numberStr.substring(0, 2) + strEnd);
    }

    public static int doCompare(int col1, int col2) {
        String excelFilePath = ReportUtilsApplication.DOC_PATH + "\\替换\\小微信保易投保情况分析月报.xlsx";
        int count = 0;
        try (FileInputStream fis = new FileInputStream(excelFilePath); Workbook workbook = WorkbookFactory.create(fis)) {
            Sheet sheet = workbook.getSheetAt(0); // 获取第一个Sheet
            for (int rowNum = 6; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row != null) {
                    Cell cell1 = row.getCell(col1);
                    Cell cell2 = row.getCell(col2);
                    Cell cell3 = row.getCell(1);
                    if (cell1 != null && cell2 != null) {
                        log.info("窗口:" + cell1.getNumericCellValue() + "线上累计:" + cell2.getNumericCellValue() + "省份:" + cell3.getStringCellValue());
                        if (cell1.getNumericCellValue() != 0 && cell1.getNumericCellValue() == cell2.getNumericCellValue() && StringUtils.isNotEmpty(cell3.getStringCellValue())) {
                            count += 1;
                        }
                    }
                }
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return count;
    }
}
